#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import datetime

import pandas as pd
import numpy as np

from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.dp_common_job import DP011MJob__df7


class DP011_M_Job(AbstractDPJob):
    """
    获取消耗信息 按照类型调用不同存储过程

    SU_AJBG_DP0102 主原料
    BPC 能耗
    BPC 锌耗

    从main函数传入参数
    p_account,p_cost_center,p_unit,p_account_period_start,p_account_period_end,p_data_type
    """

    cost_center_org = None
    data_type = None

    # NOTE p_wce_org =v_wce,
    # NOTE p_wce=LEFT(v_wce,1)||'@@@@'
    wce_org = None
    wce = None

    cal_type = None
    coef = None
    mon = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center_org=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None,
                 p_wce_org=None,
                 p_wce=None,
                 p_cal_type=None,
                 p_coef=None,
                 p_mon=None,p_df7=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center_org:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        :param p_wce_org: 成本科目编号
        :param p_wce:
        :param p_cal_type:
        :param p_coef:
        :param p_mon:
        """
        super(DP011_M_Job, self).__init__(p_config=p_config,
                                          p_db_conn_mpp=p_db_conn_mpp,
                                          p_db_conn_rds=p_db_conn_rds,
                                          p_db_conn_dbprod7=p_db_conn_dbprod7,
                                          p_unit=p_unit,
                                          p_account=p_account,
                                          p_cost_center=p_cost_center,
                                          p_account_period_start=p_account_period_start,
                                          p_account_period_end=p_account_period_end)
        self.cost_center_org = p_cost_center_org
        self.data_type = p_data_type
        self.wce_org = p_wce_org
        self.wce = p_wce
        self.cal_type = p_cal_type
        self.coef = p_coef
        self.mon = p_mon
        self.df7 = p_df7

    def do_execute(self):
        """
        """
        self.logger.info('DP011MJob.do_execute')

        V_ACCOUNT_PERIOD_START, V_ACCOUNT_PERIOD_START_MONTH, V_ACCOUNT_PERIOD_END, V_ACCOUNT_PERIOD_END_MONTH = self.__step_1()

        df1, df2, df3, df3_new = self.__step_2(p_timestamp_end=V_ACCOUNT_PERIOD_END,
                                               p_timestamp_start=V_ACCOUNT_PERIOD_START)
        success = df3_new.empty is False
        if success is False:
            return
        df5_new = self.__step_3(p_df1=df1)

        df6 = self.__step_4(df3_new=df3_new, df5_new=df5_new)

        df8 = self.__step_5(df6=df6)

        super(DP011_M_Job, self).do_execute()

    def __step_5(self, df6=None):
        # df7 = DP011MJob__df7(db_conn_dbprod7=self.db_conn_dbprod7,
        #                      db_conn_rds=self.db_conn_rds,
        #                      wce_org=self.wce_org,
        #                      data_type=self.data_type,
        #                      account=self.account,
        #                      cost_center_org=self.cost_center_org,
        #                      unit=self.unit,
        #                      account_period_start=self.account_period_start,
        #                      account_period_end=self.account_period_end)
        df7M = self.df7

        df6 = df6[(df6['WCE'] == self.wce_org)]
        df6.drop(['WCE'], axis=1, inplace=True)

        # df7 merge df6 left on COST_CENTER,ACCOUNT,PRODUCT_CODE 得到df8
        df8 = pd.merge(df7M, df6, on=['COST_CENTER', 'ACCOUNT', 'PRODUCT_CODE'], how='left')

        def __cal_ACT_N(x):
            rst = 0
            if x.UNIT_CONSUME == 0:
                rst = 0
            else:
                rst = x.UNIT_CONSUME * x.ACT_WT
            return rst

        df8['ACT_N'] = df8.apply(lambda x: __cal_ACT_N(x), axis=1)

        def __cal_CONSUME_N(x):
            rst = 0
            if x.UNIT_CONSUME == 0:
                rst = 0
            else:
                rst = x.UNIT_CONSUME * x.ACT_WT
            return rst

        df8['CONSUME_N'] = df8.apply(lambda x: __cal_CONSUME_N(x), axis=1)
        df8.drop(['YMON'], axis=1, inplace=True)
        df8.drop(['AMT'], axis=1, inplace=True)
        df8.drop(['BDGT_AMT'], axis=1, inplace=True)
        df8.drop(['BDGT_N'], axis=1, inplace=True)
        df8.drop(['EARN_N'], axis=1, inplace=True)
        df8.drop(['YEDITION'], axis=1, inplace=True)
        df8.drop(['BDGT_EARNN'], axis=1, inplace=True)
        df8.drop(['UNIT_CONSUME'], axis=1, inplace=True)

        df8.drop(['REC_ID'], axis=1, inplace=True)
        df8['REC_CREATOR'] = df8['REC_REVISOR']
        df8['REC_CREATE_TIME'] = df8['REC_REVISOR_TIME']
        df8.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df8.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df8.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df8.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df8.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df8.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df8.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df8.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df8.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df8.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df8.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df8.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df8.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df8.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df8.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df8.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df8.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df8.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        #df8.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df8.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
        df8.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
        df8.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
        df8.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
        df8.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df8.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df8.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df8.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df8.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df8.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df8.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df8.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df8.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df8.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df8.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)




        # 将df8插入到BGRAGGCB.SU_AJBG_DP0101
        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df8,
                       p_max_times=5).redo()

        return df8

    def __step_4(self, df3_new=None, df5_new=None):
        # df3_new merge df5_new left on ACCOUNT,COST_CENTER,PRODUCT_CODE,YMON 得到df6
        v = ['ACCOUNT', 'COST_CENTER', 'PRODUCT_CODE', 'YMON']
        df6 = pd.merge(df3_new, df5_new, on=v, how='left')

        def __cal_UNIT_CONSUME(x):
            rst = 0
            if x.EARN_N == 0:
                rst = 0.000000
            if x.EARN_N != 0 and x.ACT_N != 0:
                rst = x.ACT_N / x.EARN_N
            else:
                rst = x.AMT / x.EARN_N
            return rst

        df6['UNIT_CONSUME'] = df6.apply(lambda x: __cal_UNIT_CONSUME(x), axis=1)

        return df6

    def __step_3(self, p_df1=None):
        sql = " SELECT " \
              " COST_CENTER," \
              " PRODUCT_CODE," \
              " BDGT_EARNN," \
              " ACCOUNT," \
              " YEDITION" \
              " FROM BGRAGGCB.SA_CY00_ACMC00" \
              " WHERE 1=1 " \
              " AND COST_CENTER='%s'" % (self.cost_center)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        self.logger.info(df)
        success = df.empty is False
        df4 = df

        # df1 merge df4 left on ACCOUNT,YEDITION 得到df5
        df5 = pd.merge(p_df1, df4, on=['ACCOUNT', 'YEDITION'], how='left')
        v = ['YMON', 'ACCOUNT', 'COST_CENTER', 'PRODUCT_CODE']
        df5['BDGT_EARNN'].fillna(0)
        a = df5.groupby(v)['BDGT_EARNN'].agg([np.sum]).round(2)
        a.rename(columns={'sum': 'EARN_N'}, inplace=True)
        df5.drop_duplicates(subset=v, keep='first', inplace=True)
        df5_new = pd.merge(a, df5, on=v, how='left')
        self.logger.info(df5_new)

        return df5_new

    def __step_2(self, p_timestamp_end=None, p_timestamp_start=None):
        sql = " SELECT " \
              " ACCOUNT," \
              " YEDITION," \
              " YEAR||MONTH_START AS YMON" \
              " FROM " \
              " BGRAGGCB.WH_CY00_000010" \
              " WHERE 1=1 " \
              " AND ACCOUNT = '%s'" \
              " AND YEAR||MONTH_END >= LEFT('%s',6)" \
              " AND YEAR||MONTH_START <= LEFT('%s',6)" \
              " ORDER BY " \
              " MONTH_END ASC," \
              " MONTH_END DESC," \
              " EDITION DESC" \
              " FETCH FIRST 1 ROWS ONLY" % (self.account, p_timestamp_end, p_timestamp_start)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        self.logger.info(df)
        success = df.empty is False
        df1 = df

        sql = " SELECT " \
              " COST_CENTER," \
              " PRODUCT_CODE," \
              " WCE," \
              " BDGT_AMT," \
              " BDGT_N," \
              " ACCOUNT," \
              " YEDITION" \
              " FROM " \
              " BGRAGGCB.SA_CY00_ACMD00" \
              " WHERE 1=1 " \
              " AND COST_CENTER='%s'" % (self.cost_center)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        self.logger.info(df)
        success = df.empty is False
        df2 = df

        # df1 merge df2 left on ACCOUNT,YEDITION 得到df3
        df3 = pd.merge(df1, df2, on=['ACCOUNT', 'YEDITION'], how='left')
        self.logger.info(df3)

        # df31=df3.groupby YMON,ACCOUNT,COST_CENTER,PRODUCT_CODE,WCE. 得到SUM(BDGT_AMT) AS AMT, SUM(BDGT_N) AS SUM_BDGT_N
        v = ['YMON', 'ACCOUNT', 'COST_CENTER', 'PRODUCT_CODE', 'WCE']
        # df3['BDGT_AMT'].fillna(0)
        # df3['BDGT_N'].fillna(0)
        df3['BDGT_AMT'].fillna(value=0, inplace=True)
        df3['BDGT_N'].fillna(value=0, inplace=True)
        a = df3.groupby(v)['BDGT_AMT'].agg([np.sum]).round(2)
        a.rename(columns={'sum': 'AMT'}, inplace=True)
        b = df3.groupby(v)['BDGT_N'].agg([np.sum]).round(2)
        b.rename(columns={'sum': 'SUM_BDGT_N'}, inplace=True)
        c = pd.merge(a, b, on=v, how='left')
        df3.drop_duplicates(subset=v, keep='first', inplace=True)
        df3_new = pd.merge(c, df3, on=v, how='left')
        success = df3_new.empty is False
        if success is False:
            return df1, df2, df3, df3_new

        def __cal_ACT_N(x):
            # WCE == 成本科目编号
            rst = x.AMT if x.WCE[2:4] in ['2', '3', '7', '8'] else x.SUM_BDGT_N
            return rst

        df3_new['ACT_N'] = df3_new.apply(lambda x: __cal_ACT_N(x), axis=1)
        df3_new.drop(['SUM_BDGT_N'], axis=1, inplace=True)
        df3_new.drop(['YEDITION'], axis=1, inplace=True)

        self.logger.info(df3_new)

        return df1, df2, df3, df3_new

    def __step_1(self):
        # 只用到年月
        V_ACCOUNT_PERIOD_START, V_ACCOUNT_PERIOD_START_MONTH = self.__cal_22(p_timestamp_a=self.account_period_start,
                                                                             p_timestamp_b=self.account_period_start)
        V_ACCOUNT_PERIOD_END, V_ACCOUNT_PERIOD_END_MONTH = self.__cal_22(p_timestamp_a=self.account_period_start,
                                                                         p_timestamp_b=self.account_period_end)
        return V_ACCOUNT_PERIOD_START, V_ACCOUNT_PERIOD_START_MONTH, V_ACCOUNT_PERIOD_END, V_ACCOUNT_PERIOD_END_MONTH

    def __cal_22(self, p_timestamp_a=None, p_timestamp_b=None):
        """
        NOTE 注意看下面的DB2存储过程, 该函数是根据该存储过程翻译的

        处理时间将时间都转换成晚上十点
        IF RIGHT(P_ACCOUNT_PERIOD_START,6)>='220000'
        then V_ACCOUNT_PERIOD_START = TO_CHAR(TO_DATE(LEFT(P_ACCOUNT_PERIOD_START,8),'YYYYMMDD')+1 DAY,'YYYYMMDD')||'220000'
        ELSE
        SET V_ACCOUNT_PERIOD_START = LEFT(P_ACCOUNT_PERIOD_START,8)||'220000'
        end

        IF RIGHT(P_ACCOUNT_PERIOD_START,6)>='220000' THEN
        SET V_ACCOUNT_PERIOD_END = TO_CHAR(TO_DATE(LEFT(P_ACCOUNT_PERIOD_END,8),'YYYYMMDD')+1 DAY,'YYYYMMDD')||'220000'
        ELSE
        SET V_ACCOUNT_PERIOD_END = LEFT(P_ACCOUNT_PERIOD_END,8)||'220000'
        end
        FYI:看是不是大于22点 大于就是第二天的22点 否则就是当天的22点

        :param p_timestamp_a:MUST be P_ACCOUNT_PERIOD_START
        :param p_timestamp_b:should be one of P_ACCOUNT_PERIOD_START/P_ACCOUNT_PERIOD_END
        :return:
        """
        t_a = datetime.datetime.strptime(str(p_timestamp_a), '%Y%m%tmp_dict%H%M%S')
        today_22_a = datetime.datetime(year=t_a.year, month=t_a.month, day=t_a.day, hour=22).strftime('%Y%m%tmp_dict%H%M%S')
        tomorrow_a = t_a + datetime.timedelta(days=1)
        tomorrow_22_a = datetime.datetime(year=tomorrow_a.year,
                                          month=tomorrow_a.month,
                                          day=tomorrow_a.day,
                                          hour=22).strftime('%Y%m%tmp_dict%H%M%S')

        #
        t_b = datetime.datetime.strptime(str(p_timestamp_b), '%Y%m%tmp_dict%H%M%S')
        today_22_b = datetime.datetime(year=t_b.year, month=t_b.month, day=t_b.day, hour=22).strftime('%Y%m%tmp_dict%H%M%S')
        tomorrow_b = t_b + datetime.timedelta(days=1)
        tomorrow_22_b = datetime.datetime(year=tomorrow_b.year,
                                          month=tomorrow_b.month,
                                          day=tomorrow_b.day,
                                          hour=22).strftime('%Y%m%tmp_dict%H%M%S')

        if p_timestamp_a >= today_22_a:
            rst = tomorrow_22_b
        else:
            rst = today_22_b
        t_a = datetime.datetime.strptime(str(rst), '%Y%m%tmp_dict%H%M%S')
        return rst, t_a.strftime('%Y%m')
